﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using BLToolkit.Data;
using BLToolkit.Data.DataProvider;
using BLToolkit.DataAccess;
using BLToolkit.Data.Linq;

namespace nwrole.toolbox.sync.entity
{
    public class SyncEntityRepository
    {

        public string SrcConnectionString { get; set; }
        public string SyncConnectionString { get; set; }

        public SyncEntityRepository() {
        }

        public SyncEntityRepository(string srcConnectionString, string syncConnectionString) {
            SrcConnectionString = srcConnectionString;
            SyncConnectionString = syncConnectionString;
        }

        public DataProviderBase DataProvider { get; set; } // new OracleDataProvider()

        public List<SyncEntity> FindAllToSync()
        {
            // IQueryable<SyncEntityColumn> result = table.Select(a => a);
            // var test2 = table.Select(a => a.SyncEntity).FirstOrDefault();
            // var hej = result.ToList();
            List<SyncEntity> result = new List<SyncEntity>();
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Table<SyncEntityColumn> table = db.GetTable<SyncEntityColumn>();
                    
                    var allItems = table.Select(_column => new { column = _column, entity = _column.SyncEntity })
                        .Where(e => e.column.SyncEnabled == 1 && e.entity.SyncEnabled == 1)
                        .OrderBy(o1 => o1.entity.SortIndex)
                        .ThenBy(o2 => o2.entity.Name)
                        .ThenBy(o3 => o3.column.SortIndex).ToList();

                    SyncEntity currentEntity = null;
                    foreach (var item in allItems)
                    {
                        if ((currentEntity == null) || (item.entity.Id != currentEntity.Id))
                        {
                            currentEntity = item.entity;
                            result.Add(currentEntity);
                        }
                        currentEntity.Columns.Add(item.column);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindAllToSync Error: " + e);
            }
            return result;
        }

        public List<SyncEntity> FindRemainingToSync(int syncInstanceId)
        {
            List<SyncEntity> result = new List<SyncEntity>();
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    SyncInstance syncInstance = db.GetTable<SyncInstance>().SingleOrDefault(i => i.Id == syncInstanceId);
                    if (syncInstance == null)
                        return null;

                    Table<SyncEntityColumn> table = db.GetTable<SyncEntityColumn>();
                    
                    var allItems = table.Select(_column => new { column = _column, entity = _column.SyncEntity })
                        //.Where(e => e.entity.SyncInstanceId == syncInstanceId && (!e.entity.SyncDat.HasValue || e.entity.SyncDat.Value.AddHours(-1) < syncInstance.LastFullSyncStart) && e.column.SyncEnabled == 1 && e.entity.SyncEnabled == 1)
                        .Where(e => e.entity.SyncInstanceId == syncInstanceId && (!e.entity.SyncDat.HasValue || e.entity.SyncDat.Value < syncInstance.LastFullSyncStart) && e.column.SyncEnabled == 1 && e.entity.SyncEnabled == 1)
                        .OrderBy(o1 => o1.entity.SortIndex)
                        .ThenBy(o2 => o2.entity.Name)
                        .ThenBy(o3 => o3.column.SortIndex).ToList();

                    SyncEntity currentEntity = null;
                    foreach (var item in allItems)
                    {
                        if ((currentEntity == null) || (item.entity.Id != currentEntity.Id))
                        {
                            currentEntity = item.entity;
                            result.Add(currentEntity);
                        }
                        currentEntity.Columns.Add(item.column);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindAllToSync Error: " + e);
            }
            return result;
        }

        public void DeleteAll()
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    db.GetTable<SyncEntity>().Delete();
                    db.GetTable<SyncEntityColumn>().Delete();
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository DeleteAll Error: " + e);
            }
        }

        public void Insert(List<SyncEntity> list)
        {
            if (list != null)
            {
                foreach (var e in list)
                {
                    Insert(e);
                }
            }
        }

        /*public void Insert(SyncMessage msg)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, ConnectionString))
                {
                    Decimal identity = (Decimal)db.InsertWithIdentity<SyncMessage>(msg);
                }
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.ToString());
            }
        }*/


        public void Insert(SyncEntity entity)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    // int hej = db.Insert<SyncEntity>(entity);
                    Decimal identity = (Decimal)db.InsertWithIdentity<SyncEntity>(entity);
                    entity.Id = Convert.ToInt32(identity);
                    foreach (var col in entity.Columns)
                    {
                        col.SyncEntityId = Convert.ToInt32(identity);
                        db.Insert<SyncEntityColumn>(col);
                    }

                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Insert Error: " + e);
            }
        }

        /*public void Update(SyncMessage msg, Action<SyncMessage> action)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, ConnectionString))
            {
                db.BeginTransaction();

                action(msg);

                db.Update(msg);
                db.CommitTransaction();
            }
        }*/

        public void Update(SyncEntity entity)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    db.Update<SyncEntity>(entity);
                    foreach (var c in entity.Columns)
                    {
                        if (c.Id == 0)
                        {
                            //c.SyncEntityId = entity.Id;
                            Decimal identity = (Decimal)db.InsertWithIdentity<SyncEntityColumn>(c);
                            c.Id = Convert.ToInt32(identity);
                            // db.Insert(c);
                        }
                        else
                        {
                            db.Update<SyncEntityColumn>(c);
                        }

                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Update entity Error: " + e);
            }
        }

        public void Delete(SyncEntity entity)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    foreach (var c in entity.Columns)
                    {
                        if (c.Id != 0)
                        {
                            db.Delete(c);
                        }
                    }
                    db.Delete(entity);
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Delete Error: " + e);
            }
        }

        public void DeleteColumns(SyncEntity entity, List<SyncEntityColumn> columns)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    foreach (var c in columns)
                    {
                        if (c.Id != 0)
                        {
                            db.Delete(c);
                            entity.Columns.Remove(c);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository DeleteColumns Error: " + e);
            }
        }

        public void Update(SyncEntityColumn col)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    db.Update<SyncEntityColumn>(col);
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Update column Error: " + e);
            }
        }
        public SyncEntity SyncEntity_FindById(long id)
        {
            DbManager.DefaultConfiguration = ""; 
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                return db
                    .SetCommand("SELECT * FROM SyncEntity WHERE Id = @id",
                        db.Parameter("@id", id))
                    .ExecuteObject<SyncEntity>();
            }
        }

        public SyncEntityColumn SyncEntityColumn_FindById(long id)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                return db
                    .SetCommand("SELECT * FROM SyncEntityColumn WHERE Id = @id",
                        db.Parameter("@id", id))
                    .ExecuteObject<SyncEntityColumn>();
            }
        }

        public List<SyncEntityColumn> SyncEntityColumns_FindByEntityId(long syncEntityId)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                return db
                    .SetCommand("SELECT * FROM SyncEntityColumn WHERE SyncEntityId = @id",
                        db.Parameter("@id", syncEntityId))
                    .ExecuteList<SyncEntityColumn>();
            }
        }

        public SyncEntity FindBySourceTableName(string SourceTableName, bool loadRef = false)
        {
            SyncEntity result = null;
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                result = db
                    .SetCommand("SELECT * FROM SyncEntity WHERE SourceTableName = @SourceTableName",
                        db.Parameter("@SourceTableName", SourceTableName))
                    .ExecuteObject<SyncEntity>();
                if (loadRef && (result!=null))
                {
                    result.Columns = db
                        .SetCommand("SELECT * FROM SyncEntityColumn WHERE SyncEntityId = @id",
                            db.Parameter("@id", result.Id))
                        .ExecuteList<SyncEntityColumn>();
                }
            }
            return result;
        }

        public SyncEntity FindByName(string name)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                return db
                    .SetCommand("SELECT * FROM SyncEntity WHERE Name = @name",
                        db.Parameter("@name", name))
                    .ExecuteObject<SyncEntity>();
            }
        }

        public SyncEntity FindByNameWithRef(string name)
        {
            SyncEntity result = new SyncEntity();
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Table<SyncEntityColumn> table = db.GetTable<SyncEntityColumn>();
                    var allItems = table.Select(_column => new { column = _column, entity = _column.SyncEntity })
                        .Where(e => e.entity.Name == name).ToList();
                    foreach (var item in allItems)
                    {
                        if ((result == null) || (item.entity.Id != result.Id))
                        {
                            result = item.entity;
                        }
                        result.Columns.Add(item.column);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindByNameWithRef Error: " + e);
            }
            return result;
        }


        public SyncEntity FindByNameObjectId(long objectId)
        {
            SyncEntity result = new SyncEntity();
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Table<SyncEntityColumn> table = db.GetTable<SyncEntityColumn>();
                    var allItems = table.Select(_column => new { column = _column, entity = _column.SyncEntity })
                        .Where(e => e.entity.ExternalObjectId == objectId).ToList();
                    foreach (var item in allItems)
                    {
                        if ((result == null) || (item.entity.Id != result.Id))
                        {
                            result = item.entity;
                        }
                        result.Columns.Add(item.column);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindByNameObjectId Error: " + e);
            }
            return result;
        }

        public SyncEntity FindByIdWithRef(long id)
        {
            SyncEntity currentEntity = null;
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Table<SyncEntityColumn> table = db.GetTable<SyncEntityColumn>();
                    var allItems = table.Select(_column => new { column = _column, entity = _column.SyncEntity })
                        .Where(e => e.entity.Id == id)
                        .OrderBy(o1 => o1.entity.SortIndex)
                        .ThenBy(o2 => o2.entity.Name)
                        .ThenBy(o3 => o3.column.SortIndex).ToList();

                    foreach (var item in allItems)
                    {
                        if ((currentEntity == null) || (item.entity.Id != currentEntity.Id))
                        {
                            currentEntity = item.entity;
                        }
                        currentEntity.Columns.Add(item.column);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindByIdWithRef Error: " + e);
            }
            return currentEntity;
        }

        public List<SyncEntity> FindAll(int syncInstanceId)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                return db
                    .SetCommand("SELECT * FROM SyncEntity WHERE SyncInstanceId="+syncInstanceId)
                    .ExecuteList<SyncEntity>();
            }
        }

        public List<SyncEntity> SchemaFindAll()
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SrcConnectionString))
            {
                return db
                    .SetCommand("select object_id as Id, 1 as Sortindex, Name as Name, name as SourceTableName, name as ShadowTableName, name as DestinationTableName, 0 as SyncEnabled, 0 as [Type] from sys.tables " +
                                "union " +
                                "select object_id as Id, 1 as Sortindex, Name as Name, name as SourceTableName, name as ShadowTableName, name as DestinationTableName, 0 as SyncEnabled, 1 as [Type] from sys.views " +
                                "order by [Type], Name")
                    .ExecuteList<SyncEntity>();
            }
        }

        public SyncEntity SchemaFindById(long id, short type)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SrcConnectionString))
            {
//                List<SyncEntityColumn> list = db
//                    .SetCommand(
//                                @"SELECT c.column_id as id, object_id as SyncEntityId, 1 as SortIndex, c.name as ColumnName,
//null as CSharpType, t.name as SourceSqlTypeName, t.name as DestSqlTypeName,
//c.is_nullable as SourceSqlTypeNullable, c.is_nullable as DestSqlTypeNullable, is_identity as PrimaryKey, 0 as SyncEnabled,
//c.max_length as SourceSqlTypeMaxLength, c.max_length as DestSqlTypeMaxLength
//FROM sys.columns c 
//inner join sys.types t
//on c.user_type_id = t.user_type_id
//where c.object_id = @id", db.Parameter("@id", id))
//                    .ExecuteList<SyncEntityColumn>();

                List<SyncEntityColumn> list = db
                    .SetCommand(
                                @"SELECT c.column_id as id, c.object_id as SyncEntityId, 1 as SortIndex, c.name as ColumnName,
null as CSharpType, t.name as SourceSqlTypeName, t.name as DestSqlTypeName,
c.is_nullable as SourceSqlTypeNullable, c.is_nullable as DestSqlTypeNullable, is_identity as PrimaryKey, 0 as SyncEnabled,
cols.character_maximum_length as SourceSqlTypeMaxLength, cols.character_maximum_length as DestSqlTypeMaxLength, c.precision as DestSqlTypePrecision, c.scale AS DestSqlTypeScale
FROM sys.columns c 
inner join sys.objects o on c.object_id =  o.object_id
inner join information_schema.columns cols on cols.column_name = c.name and cols.table_name = o.name
inner join sys.types t
on c.system_type_id = t.system_type_id and t.user_type_id = t.system_type_id 
where c.object_id = @id", db.Parameter("@id", id))
                    .ExecuteList<SyncEntityColumn>();

                SyncEntity result = db.SetCommand(@"select object_id as Id, 1 as Sortindex, Name as Name, name as SourceTableName, null as ShadowTableName, 
                                        null as DestinationTableName, 0 as SyncEnabled from " + (type == 0 ? "sys.tables" : "sys.views") + " where object_id = @id", db.Parameter("@id", id))
                                        .ExecuteObject<SyncEntity>();
                
                result.Columns = list;
                return result;
            }
        }

        public List<SyncEntity> FindAllOld()
        {
            List<SyncEntity> list = new List<SyncEntity>();

            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {

                db.SetCommand("select * from SyncEntity se inner join SyncEntityColumn sec on se.Id = sec.SyncEntityId order by SyncEntityId");
                var reader = db.ExecuteReader();

                long lastId = 0;
                SyncEntity currentEntity = null;
                while (reader.NextResult())
                {
                    long id = (long)reader["Id"];
                    if (id != lastId)
                    {
                        currentEntity = new SyncEntity();
                    }
                    SyncEntityColumn sec = new SyncEntityColumn();
                    // currentEntity.Columns.Add(sec);
                }
            }

            return null;
        }

        public List<SyncInstance> FindAllSyncInstances()
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                Table<SyncInstance> table = db.GetTable<SyncInstance>();

                return table.ToList();
            }
        }

        public SyncInstance FindSyncInstance(int instanceId)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    SyncInstance instance = db.GetTable<SyncInstance>().SingleOrDefault(i => i.Id == instanceId);

                    return instance;
                }
            }
            catch(Exception e)
            {
                Trace.TraceError("SyncEntityRepository FindSyncInstance id:" + instanceId + " exception:" + e);
                throw;
            }
        }

        public void Insert(SyncInstance instance)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Decimal identity = (Decimal)db.InsertWithIdentity<SyncInstance>(instance);
                    instance.Id = Convert.ToInt32(identity);
                    foreach (var entity in instance.Entities)
                    {
                        entity.SyncInstanceId = Convert.ToInt32(identity);
                        db.Insert(entity);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Insert SyncInstance Error: " + e);
            }
        }

        public void Update(SyncInstance instance)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    db.Update<SyncInstance>(instance);
                    /*foreach (var c in entity.Columns)
                    {
                        if (c.Id == 0)
                        {
                            //c.SyncEntityId = entity.Id;
                            Decimal identity = (Decimal)db.InsertWithIdentity<SyncEntityColumn>(c);
                            c.Id = Convert.ToInt32(identity);
                            // db.Insert(c);
                        }
                        else
                        {
                            db.Update<SyncEntityColumn>(c);
                        }

                    }*/
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Update SyncInstance Error: " + e);
            }
        }

        public DateTime GetDbDateTime()
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {

                db.SetCommand("select GETDATE()");
                DateTime result = (DateTime)db.ExecuteScalar();

                return result;
            }
        }

        public bool TableExist(string tableName)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                db.SetCommand(string.Format(@"select count(*) from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName));
                int result = db.ExecuteNonQuery();
                return result == 1;
            }
        }

        private SyncSetting GetSetting(string key)
        {
            DbManager.DefaultConfiguration = "";
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                Table<SyncSetting> table = db.GetTable<SyncSetting>();

                return table.FirstOrDefault(s => s.Key == key);
            }
        }

        public string GetSettingStringValue(string key)
        {
            SyncSetting setting = GetSetting(key);
            if (setting == null)
            {
                throw (new ApplicationException("SyncSetting key not found: " + key));
            }

            return setting.Value;
        }

        public void SetSettingValue(string key, string value)
        {
            try
            {
                DbManager.DefaultConfiguration = "";
                using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
                {
                    Table<SyncSetting> table = db.GetTable<SyncSetting>();

                    SyncSetting setting = table.FirstOrDefault(s => s.Key == key);
                    if (setting == null)
                    {
                        //throw (new ApplicationException("SyncSetting key not found: " + key));
                        setting = new SyncSetting();
                        setting.Key = key;
                        setting.Value = value;
                        setting.CreatedDt = DateTime.UtcNow;

                        db.Insert<SyncSetting>(setting);
                    }
                    else
                    {
                        setting.Value = value;
                        setting.UpdatedDt = DateTime.UtcNow;

                        db.Update<SyncSetting>(setting);
                    }
                }
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncEntityRepository Update SyncInstance Error: " + e);
            }
        }

        /*public void TestTimeStamp()
        {
            System.Data.Linq.Binary stamp;

            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                db.SetCommand(@"select teststamp from syncentity where id=26");
                //db.SetCommand(@"select teststamp from syncentity where teststamp=@TheParam", db.Parameter("@TheParam", stamp, System.Data.DbType.Binary));
                    //db.SetCommand(sql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                stamp = db.ExecuteScalar<System.Data.Linq.Binary>();
                
            }

            SyncEntity entity;
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                
                //db.SetCommand(@"select * from syncentity");
                entity = db.GetTable<SyncEntity>().SingleOrDefault(e => e.Id == 26);

                entity.TestStampCopy = stamp;
            }

            Update(entity);


            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {
                //db.SetCommand(@"select teststamp from syncentity where id=26");
                db.SetCommand(@"select teststamp from syncentity where id=26 and teststamp > @TheParam", db.Parameter("@TheParam", stamp, System.Data.DbType.Binary));
                //db.SetCommand(sql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                stamp = db.ExecuteScalar<System.Data.Linq.Binary>();

            }

            entity = null;
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(DataProvider, SyncConnectionString))
            {

                //db.SetCommand(@"select * from syncentity");
                entity = db.GetTable<SyncEntity>().SingleOrDefault(e => e.Id == 26);

                entity.TestStampCopy = stamp;
            }

            Update(entity);
        }*/
    }
}
